import sqlite3 as sqlite


def db_connect(db_name):
    """ connection to db or create new database"""
    pass    

def db_create_structure(db_cur):
   # first: drop all table
    db_cur.execute("SELECT name from sqlite_master where name='sites'")
    if db_cur.fetchone():
        db_cur.execute('DROP TABLE sites')
        db_cur.execute('DROP TABLE keywords')
        db_cur.execute('DROP TABLE position')

   # second: create all table
    db_cur.execute("""
        CREATE TABLE sites (
            id integer primary key, 
            site_name text
        )""")

    db_cur.execute("""
        CREATE TABLE keywords (
            id integer primary key,
            site_id integer,
            keyword text
        )""")

    # adding if nessesary 'site_id integer'
    db_cur.execute("""
        CREATE TABLE position (
            id integer primary key,
            date text,
            keyword_id integer,
            position integer
        )""")

def db_get_sites(db_cur):
    db_cur.execute("SELECT site_name FROM sites")
    return db_cur.fetchall()


def db_get_keywords(db_cur, site_name):
    db_cur.execute("SELECT keyword FROM keywords WHERE site_id = (SELECT id FROM sites WHERE site_name = '" + site_name + "')")

# TODO: refactoring
# TODO: date as class???
def db_keywords_position(db_cur, key_id, *range_date):
# arguments: key id, start date, end date
# non constant count of function argument
# 'site_name' is really need?
    def valid_date(date):
        pass
    #check additional args
    if len(range_date) >= 2 and valid_date(range_date):
        # if date1 > date2 excange date
        if range_date[1] > range_date[2]:
            tmp_rd = range_date[1]
            range_date[1] = range_date[2]
            range_date[2] = range_date[1]
        # get query
        tmp_pos = db_cur.execute("SELECT * FROM positions WHERE keyword_id = key_id")
        for row in tmp_pos:
            if row['date'] > range_date[1] and row['date'] < range_date[2]:
                pass

def test_insert_data(db_cur):
    db_cur.execute("""insert into sites values (null, 'miraccum.ru')""")
    db_cur.execute("""insert into sites values (null, 'richy-richy.ru')""")
    db_cur.execute("""insert into sites values (null, 'mireks.ru')""")
    db_cur.execute("""insert into keywords values (null, 1, 'key1')""")
    db_cur.execute("""insert into keywords values (null, 2, 'key2')""")
    db_cur.execute("""insert into keywords values (null, 2, 'key2_1')""")
    db_cur.execute("""insert into position values (null, '2011-09-28', 1, 8)""")
    db_cur.execute("""insert into position values (null, '2011-09-29', 1, 5)""")
    db_cur.execute("""insert into position values (null, '2011-09-30', 1, 3)""")
    db_cur.execute("""insert into position values (null, '2011-09-28', 2, 3)""")
    db_cur.execute("""insert into position values (null, '2011-09-29', 2, 4)""")
	

if __name__ == '__main__':
    db_name = 'positions.db'
    db_conn = sqlite.connect(db_name)
    db_cursor = db_conn.cursor()
    
    # db_create_structure(db_cursor)

# ------------- test -------------------

    test_insert_data(db_cursor)
    db_cursor.execute("""select * from position""")
    for row in db_cursor:
        print row

    db_cursor.execute("""select * from sites""")
    for row in db_cursor:
        print row

    print 'row 1: ', row[1]
    print db_get_keywords(db_cursor, 'richy-richy.ru')

# ------------- test -------------------

    db_conn.commit()
    db_cursor.close()
    db_conn.close()
